package com.statistics.yfdata.dao;

import com.statistics.bean.Electricity;
import com.statistics.bean.ElectricityWeek;
import com.statistics.bean.MaxCurrentPower;
import com.statistics.yfdata.entity.DataElectricityEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author MәӧωρaЯsε
 * @date 2017/7/20.
 */
@Repository
public class DataElectricityRepository {

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate primaryJdbcTemplate;

    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    private JdbcTemplate secondaryJdbcTemplate;

    private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public Electricity getSubtraction( String mac, long start, long end ,String month) {

        String tableName = "history_" + month;

        String sql = "select max.room1_electricity-min.room1_electricity as room1_electricity," +
                "max.room2_electricity-min.room2_electricity as room2_electricity," +
                "max.room3_electricity-min.room3_electricity as room3_electricity," +
                "max.room4_electricity-min.room4_electricity as room4_electricity ," +
                "max.room5_electricity-min.room5_electricity as room5_electricity," +
                "max.room7_electricity-min.room7_electricity as room7_electricity," +
                "max.room6_electricity-min.room6_electricity as room6_electricity, " +
                "max.room8_electricity-min.room8_electricity as room8_electricity " +
                "from (select * from " + tableName + " where mac = ? and created_at > ? and created_at < ? ORDER BY id DESC limit 1) as max," +
                "(select * from " + tableName + " where mac = ? and created_at > ? and created_at < ? ORDER BY id limit 1) as min";

        RowMapper<Electricity> rm = BeanPropertyRowMapper.newInstance(Electricity.class);
        List<Electricity> electricities = secondaryJdbcTemplate.query(sql, new Object[]{mac, start, end, mac, start, end}, rm);

        if(electricities.size() == 0)
            return null;
        return electricities.get(0);

    }

    public List<DataElectricityEntity> findByMac(String mac, long start, long end ,String month){
        String sql = "select * from history_" + month + " where mac = ? and created_at > ? and created_at < ?";
        RowMapper<DataElectricityEntity> rm = BeanPropertyRowMapper.newInstance(DataElectricityEntity.class);
        return secondaryJdbcTemplate.query(sql,new Object[]{mac, start, end},rm);
    }

    public MaxCurrentPower getMaxCurrentPower(String mac, long start, long end , String month){
        String sql = "SELECT max(room1_current) as room1_current, max(room2_current) as room2_current," +
                " max(room3_current) as room3_current, max(room4_current) as room4_current, max(room5_current) as room5_current," +
                " max(room6_current) as room6_current, max(room7_current) as room7_current," +
                " max(room8_current) as room8_current ,max(room1_power) as room1_power, max(room2_power) as room2_power," +
                " max(room3_power) as room3_power, max(room4_power) as room4_power," +
                " max(room5_power) as room5_power, max(room6_power) as room6_power, max(room7_power) as room7_power," +
                " max(room8_power) as room8_power FROM history_" + month + " WHERE " +
                "mac = ? AND created_at > ? AND created_at < ?";

        RowMapper<MaxCurrentPower> rm = BeanPropertyRowMapper.newInstance(MaxCurrentPower.class);
        List<MaxCurrentPower> maxCurrentPowers = secondaryJdbcTemplate.query(sql, new Object[]{mac, start, end},rm);

        if(maxCurrentPowers.size() == 0)
            return null;
        return maxCurrentPowers.get(0);
    }

    public List<ElectricityWeek> getSubtractionByWeek(String mac, long start, long end ) {

        String start_time = simpleDateFormat.format(start);
        String end_time = simpleDateFormat.format(end);

        String sql = "select sum(total_electricity) as total_electricity,offset," +
                     "max(max_power) as max_power,max(max_current) as max_current,offset " +
                     "from tbl_electricity_statistics_day where mac = ? and statistics_time > ? " +
                     "and statistics_time < ? GROUP BY `offset`";
        RowMapper<ElectricityWeek> rm = BeanPropertyRowMapper.newInstance(ElectricityWeek.class);
        List<ElectricityWeek> electricities = primaryJdbcTemplate.query(sql, new Object[]{mac, start_time, end_time}, rm);

        if(electricities.size() == 8){
            return electricities;
        }
        return null;
    }

}

